/*
 This file is part of Musimage, a project which makes possible listen to music
 and, at the same time, watch pictures related with that music.
 Copyright (C) 2006  David Garcia Narbona 

 Musimage is free software; you can redistribute it and/or modify
 it under the terms of the GNU General Public License as published by
 the Free Software Foundation; either version 2 of the License, or
 (at your option) any later version.

 Musimage is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU General Public License for more details.

 You should have received a copy of the GNU General Public License
 along with Musimage; if not, write to the Free Software
 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
 */
package classifyLyrics;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import java.util.Enumeration;
import java.util.Vector;

// Clase que engloba todo el acceso a los datos de la base de datos
// por parte de los diferentes threads del servidor
public class DataBaseManager extends DataBaseConnection {

	// ** Atributos **////////////////////////////////////////
	static private DataBaseManager dataBaseManager;

	// ///////////////////////////////////////////////////////

	// ** Metodos **//////////////////////////////////////////

	// Constructor
	public DataBaseManager() {

		super();
		dataBaseManager = null;
	}

	// Metodo para obtener la unica instancia existente de esta clase
	public static DataBaseManager getInstance() {

		if (dataBaseManager == null) {

			dataBaseManager = new DataBaseManager();
		}

		return dataBaseManager;
	}

	public void updateDataBase(Vector data) {

		Enumeration e = data.elements();

		while (e.hasMoreElements()) {

			Vector songData = (Vector) e.nextElement();

			insertSong((String) songData.get(0), (String) songData.get(1),
					(String) songData.get(2));

			insertLink((String) songData.get(0), (String) songData.get(1),
					(String) songData.get(3));
		}
	}

	// Metodo que inserta la cancion en la tabla canciones
	private void insertSong(String _autor, String _titulo, String _year) {

		try {
			// obtiene una conexion del pool de conexiones
			Connection connection = dataSource.getConnection();

			// crea un objeto Statement sobre la conexion
			Statement statement = connection.createStatement();

			String autor = _autor.toLowerCase();
			String titulo = _titulo.toLowerCase();

			Integer i = new Integer(_year);
			int year = i.intValue();

			String query = new String(
					"insert into canciones(autor, titulo, year) values('"
							+ autor + "','" + titulo + "'," + year + ");");

			statement.executeUpdate(query);

			statement.close();
			connection.close();
		} catch (Exception e) {

			System.err
					.println("Error in DataBaseManager class on insertSong method "
							+ e.getClass() + " said:'" + e.getMessage() + "'\n");
		}
	}

	private void insertLink(String autor, String titulo, String categoria) {

		int id_categoria = 0;
		int id_cancion = 0;

		try {
			// obtiene una conexion del pool de conexiones
			Connection connection = dataSource.getConnection();

			// crea un objeto Statement sobre la conexion
			Statement statement = connection.createStatement();

			// Recupera el id de la categoria a la que pertenece la cancion
			String query = new String(
					"select id from categorias_c where categoria='" + categoria
							+ "';");

			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next())
				id_categoria = resultSet.getInt(1);

			// Recupera el id de la cancion recien insertada
			query = new String("select count(*) from canciones;");

			resultSet = statement.executeQuery(query);

			while (resultSet.next())
				id_cancion = resultSet.getInt(1);

			query = new String(
					"insert into categorias_canciones(id_cancion, id_categoria) values ("
							+ id_cancion + "," + id_categoria + ");");

			statement.executeUpdate(query);

			statement.close();
			connection.close();
		} catch (Exception e) {

			System.err
					.println("Error in DataBaseManager class on insertLink method "
							+ e.getClass() + " said:'" + e.getMessage() + "'\n");
		}
	}
}
